"""
查询构建器模块
提供灵活的数据库查询构建功能
"""

from typing import List, Dict, Any, Optional, Tuple
from datetime import datetime, date


class QueryBuilder:
    """SQL 查询构建器"""
    
    def __init__(self):
        """初始化查询构建器"""
        self.reset()
    
    def reset(self):
        """重置查询构建器"""
        self._select_fields = []
        self._from_table = ""
        self._where_conditions = []
        self._where_params = []
        self._order_by = []
        self._group_by = []
        self._having_conditions = []
        self._having_params = []
        self._limit_count = None
        self._offset_count = None
    
    def select(self, fields: List[str] = None) -> 'QueryBuilder':
        """
        设置 SELECT 字段
        
        Args:
            fields: 字段列表，默认为 ['*']
            
        Returns:
            查询构建器实例
        """
        self._select_fields = fields or ['*']
        return self
    
    def from_table(self, table: str) -> 'QueryBuilder':
        """
        设置 FROM 表名
        
        Args:
            table: 表名
            
        Returns:
            查询构建器实例
        """
        self._from_table = table
        return self
    
    def where(self, condition: str, *params) -> 'QueryBuilder':
        """
        添加 WHERE 条件
        
        Args:
            condition: 条件字符串
            *params: 参数值
            
        Returns:
            查询构建器实例
        """
        self._where_conditions.append(condition)
        self._where_params.extend(params)
        return self
    
    def where_equals(self, field: str, value: Any) -> 'QueryBuilder':
        """
        添加等值条件
        
        Args:
            field: 字段名
            value: 值
            
        Returns:
            查询构建器实例
        """
        return self.where(f"{field} = ?", value)
    
    def where_in(self, field: str, values: List[Any]) -> 'QueryBuilder':
        """
        添加 IN 条件
        
        Args:
            field: 字段名
            values: 值列表
            
        Returns:
            查询构建器实例
        """
        if not values:
            return self
        
        placeholders = ','.join(['?'] * len(values))
        return self.where(f"{field} IN ({placeholders})", *values)
    
    def where_like(self, field: str, pattern: str) -> 'QueryBuilder':
        """
        添加 LIKE 条件
        
        Args:
            field: 字段名
            pattern: 模式字符串
            
        Returns:
            查询构建器实例
        """
        return self.where(f"{field} LIKE ?", pattern)
    
    def where_between(self, field: str, start: Any, end: Any) -> 'QueryBuilder':
        """
        添加 BETWEEN 条件
        
        Args:
            field: 字段名
            start: 开始值
            end: 结束值
            
        Returns:
            查询构建器实例
        """
        return self.where(f"{field} BETWEEN ? AND ?", start, end)
    
    def where_null(self, field: str, is_null: bool = True) -> 'QueryBuilder':
        """
        添加 NULL 条件
        
        Args:
            field: 字段名
            is_null: 是否为 NULL
            
        Returns:
            查询构建器实例
        """
        condition = f"{field} IS NULL" if is_null else f"{field} IS NOT NULL"
        return self.where(condition)
    
    def where_date_range(self, field: str, start_date: Optional[str] = None, 
                        end_date: Optional[str] = None) -> 'QueryBuilder':
        """
        添加日期范围条件
        
        Args:
            field: 日期字段名
            start_date: 开始日期
            end_date: 结束日期
            
        Returns:
            查询构建器实例
        """
        if start_date and end_date:
            return self.where_between(field, start_date, end_date)
        elif start_date:
            return self.where(f"{field} >= ?", start_date)
        elif end_date:
            return self.where(f"{field} <= ?", end_date)
        
        return self
    
    def order_by(self, field: str, direction: str = 'ASC') -> 'QueryBuilder':
        """
        添加排序条件
        
        Args:
            field: 排序字段
            direction: 排序方向 (ASC/DESC)
            
        Returns:
            查询构建器实例
        """
        direction = direction.upper()
        if direction not in ['ASC', 'DESC']:
            direction = 'ASC'
        
        self._order_by.append(f"{field} {direction}")
        return self
    
    def group_by(self, field: str) -> 'QueryBuilder':
        """
        添加分组条件
        
        Args:
            field: 分组字段
            
        Returns:
            查询构建器实例
        """
        self._group_by.append(field)
        return self
    
    def having(self, condition: str, *params) -> 'QueryBuilder':
        """
        添加 HAVING 条件
        
        Args:
            condition: 条件字符串
            *params: 参数值
            
        Returns:
            查询构建器实例
        """
        self._having_conditions.append(condition)
        self._having_params.extend(params)
        return self
    
    def limit(self, count: int, offset: int = 0) -> 'QueryBuilder':
        """
        设置限制条件
        
        Args:
            count: 限制数量
            offset: 偏移量
            
        Returns:
            查询构建器实例
        """
        self._limit_count = count
        self._offset_count = offset
        return self
    
    def build(self) -> Tuple[str, List[Any]]:
        """
        构建 SQL 查询语句
        
        Returns:
            (SQL 语句, 参数列表)
        """
        if not self._from_table:
            raise ValueError("必须指定 FROM 表名")
        
        # 构建 SELECT 部分
        select_clause = "SELECT " + ", ".join(self._select_fields)
        
        # 构建 FROM 部分
        from_clause = f"FROM {self._from_table}"
        
        # 构建 WHERE 部分
        where_clause = ""
        if self._where_conditions:
            where_clause = "WHERE " + " AND ".join(self._where_conditions)
        
        # 构建 GROUP BY 部分
        group_by_clause = ""
        if self._group_by:
            group_by_clause = "GROUP BY " + ", ".join(self._group_by)
        
        # 构建 HAVING 部分
        having_clause = ""
        if self._having_conditions:
            having_clause = "HAVING " + " AND ".join(self._having_conditions)
        
        # 构建 ORDER BY 部分
        order_by_clause = ""
        if self._order_by:
            order_by_clause = "ORDER BY " + ", ".join(self._order_by)
        
        # 构建 LIMIT 部分
        limit_clause = ""
        if self._limit_count is not None:
            if self._offset_count:
                limit_clause = f"LIMIT {self._limit_count} OFFSET {self._offset_count}"
            else:
                limit_clause = f"LIMIT {self._limit_count}"
        
        # 组合所有部分
        clauses = [select_clause, from_clause]
        if where_clause:
            clauses.append(where_clause)
        if group_by_clause:
            clauses.append(group_by_clause)
        if having_clause:
            clauses.append(having_clause)
        if order_by_clause:
            clauses.append(order_by_clause)
        if limit_clause:
            clauses.append(limit_clause)
        
        sql = " ".join(clauses)
        params = self._where_params + self._having_params
        
        return sql, params


class TaskQueryBuilder:
    """任务查询构建器"""
    
    def __init__(self):
        """初始化任务查询构建器"""
        self.builder = QueryBuilder()
        self.builder.select().from_table('tasks')
    
    def filter_by_status(self, statuses: List[str]) -> 'TaskQueryBuilder':
        """
        按状态过滤
        
        Args:
            statuses: 状态列表
            
        Returns:
            任务查询构建器实例
        """
        if statuses:
            self.builder.where_in('status', statuses)
        return self
    
    def filter_by_priority(self, priorities: List[str]) -> 'TaskQueryBuilder':
        """
        按优先级过滤
        
        Args:
            priorities: 优先级列表
            
        Returns:
            任务查询构建器实例
        """
        if priorities:
            self.builder.where_in('priority', priorities)
        return self
    
    def filter_by_category(self, categories: List[str]) -> 'TaskQueryBuilder':
        """
        按分类过滤
        
        Args:
            categories: 分类列表
            
        Returns:
            任务查询构建器实例
        """
        if categories:
            self.builder.where_in('category', categories)
        return self
    
    def filter_by_due_date_range(self, start_date: Optional[str] = None, 
                                end_date: Optional[str] = None) -> 'TaskQueryBuilder':
        """
        按截止日期范围过滤
        
        Args:
            start_date: 开始日期
            end_date: 结束日期
            
        Returns:
            任务查询构建器实例
        """
        self.builder.where_date_range('due_date', start_date, end_date)
        return self
    
    def filter_overdue(self) -> 'TaskQueryBuilder':
        """
        过滤过期任务
        
        Returns:
            任务查询构建器实例
        """
        current_time = datetime.now().isoformat()
        self.builder.where("due_date IS NOT NULL AND due_date < ? AND status != 'completed'", current_time)
        return self
    
    def filter_upcoming(self, days: int = 7) -> 'TaskQueryBuilder':
        """
        过滤即将到期的任务
        
        Args:
            days: 未来天数
            
        Returns:
            任务查询构建器实例
        """
        from datetime import timedelta
        
        current_time = datetime.now()
        future_time = current_time + timedelta(days=days)
        
        self.builder.where(
            "due_date IS NOT NULL AND due_date BETWEEN ? AND ? AND status != 'completed'",
            current_time.isoformat(),
            future_time.isoformat()
        )
        return self
    
    def filter_today(self) -> 'TaskQueryBuilder':
        """
        过滤今天的任务
        
        Returns:
            任务查询构建器实例
        """
        today = date.today().isoformat()
        tomorrow = (date.today() + timedelta(days=1)).isoformat()
        
        self.builder.where_between('due_date', today, tomorrow)
        return self
    
    def search_text(self, keyword: str, fields: List[str] = None) -> 'TaskQueryBuilder':
        """
        文本搜索
        
        Args:
            keyword: 搜索关键词
            fields: 搜索字段列表，默认为 ['title', 'description']
            
        Returns:
            任务查询构建器实例
        """
        if not keyword.strip():
            return self
        
        fields = fields or ['title', 'description']
        search_pattern = f'%{keyword}%'
        
        # 构建搜索条件
        conditions = []
        params = []
        
        for field in fields:
            conditions.append(f"{field} LIKE ?")
            params.append(search_pattern)
        
        if conditions:
            search_condition = "(" + " OR ".join(conditions) + ")"
            self.builder.where(search_condition, *params)
        
        return self
    
    def sort_by(self, field: str, direction: str = 'ASC') -> 'TaskQueryBuilder':
        """
        排序
        
        Args:
            field: 排序字段
            direction: 排序方向
            
        Returns:
            任务查询构建器实例
        """
        # 特殊处理优先级排序
        if field == 'priority':
            # 使用 CASE 语句将优先级转换为数字进行排序
            priority_order = """
                CASE priority 
                    WHEN 'high' THEN 3 
                    WHEN 'medium' THEN 2 
                    WHEN 'low' THEN 1 
                    ELSE 0 
                END
            """
            self.builder.order_by(priority_order, direction)
        else:
            self.builder.order_by(field, direction)
        
        return self
    
    def limit_results(self, count: int, offset: int = 0) -> 'TaskQueryBuilder':
        """
        限制结果数量
        
        Args:
            count: 限制数量
            offset: 偏移量
            
        Returns:
            任务查询构建器实例
        """
        self.builder.limit(count, offset)
        return self
    
    def build(self) -> Tuple[str, List[Any]]:
        """
        构建查询语句
        
        Returns:
            (SQL 语句, 参数列表)
        """
        return self.builder.build()


def create_task_filter(filters: Dict[str, Any]) -> TaskQueryBuilder:
    """
    创建任务过滤器
    
    Args:
        filters: 过滤条件字典
        
    Returns:
        任务查询构建器实例
    """
    query = TaskQueryBuilder()
    
    # 状态过滤
    if 'status' in filters:
        statuses = filters['status']
        if isinstance(statuses, str):
            statuses = [statuses]
        query.filter_by_status(statuses)
    
    # 优先级过滤
    if 'priority' in filters:
        priorities = filters['priority']
        if isinstance(priorities, str):
            priorities = [priorities]
        query.filter_by_priority(priorities)
    
    # 分类过滤
    if 'category' in filters:
        categories = filters['category']
        if isinstance(categories, str):
            categories = [categories]
        query.filter_by_category(categories)
    
    # 日期范围过滤
    if 'due_date_start' in filters or 'due_date_end' in filters:
        query.filter_by_due_date_range(
            filters.get('due_date_start'),
            filters.get('due_date_end')
        )
    
    # 特殊过滤器
    if filters.get('overdue'):
        query.filter_overdue()
    
    if filters.get('upcoming'):
        days = filters.get('upcoming_days', 7)
        query.filter_upcoming(days)
    
    if filters.get('today'):
        query.filter_today()
    
    # 文本搜索
    if 'search' in filters:
        search_fields = filters.get('search_fields', ['title', 'description'])
        query.search_text(filters['search'], search_fields)
    
    # 排序
    if 'sort_by' in filters:
        direction = filters.get('sort_direction', 'ASC')
        query.sort_by(filters['sort_by'], direction)
    
    # 分页
    if 'limit' in filters:
        offset = filters.get('offset', 0)
        query.limit_results(filters['limit'], offset)
    
    return query